package com.thunder.reader.provider;

import java.io.FileNotFoundException;

import com.thunder.reader.FeedSettings;

import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.net.Uri;
import android.os.ParcelFileDescriptor;
import android.os.SystemClock;
import android.text.TextUtils;
import android.util.Log;

public class FeedProvider extends ContentProvider {
    private static final String TAG = "FeedProvider";
    private static final String DATABASE_NAME = "rssfeed.db";
    private static final int DATABASE_VERSION = 1;
    /* package */ static final String AUTHORITY = "com.thunder.reader.provider.FeedProvider";
    private static final String DEFAULT_SORT_ORDER = "title ASC";
    public static final String ARTICLE_ORDER = "pub_date DESC";
    private FeedDatabaseHelper mOpenHelper;
    private ImageDownloadServer mDownloadServer;
    
    /* package */ static final String[] TABLES = {
        "groups", "sources", "articles", "images", "weibo_accounts"
    };
    
    // NOTE: MUST make sure these indices match ones of tables
    private static final int URI_MATCH_GROUP = 0;
    private static final int URI_MATCH_SOURCE = 1;
    private static final int URI_MATCH_ARTICLE = 2;
    private static final int URI_MATCH_IMAGE = 3;
    private static final int URI_MATCH_WEIBO_ACCOUNT = 4;
    private static final int URI_MATCH_MINIMIUM = URI_MATCH_GROUP;
    private static final int URI_MATCH_MAXIMIUM = URI_MATCH_WEIBO_ACCOUNT;
    // (id % 10) SHOULD match the tables, i.e. "groups/3" should also match table 'group'
    private static final int URI_MATCH_GROUP_ID = 10;
    private static final int URI_MATCH_SOURCE_ID = 11;
    private static final int URI_MATCH_ARTICLE_ID = 12;
    private static final int URI_MATCH_IMAGE_ID = 13;
    private static final int URI_MATCH_WEIBO_ACCOUNT_ID = 14;
    private static final int URI_MATCH_ID_MINIMIUM = URI_MATCH_GROUP_ID;
    private static final int URI_MATCH_ID_MAXIMIUM = URI_MATCH_WEIBO_ACCOUNT_ID;
    
    // Indices
    private static final int[] URI_INDICES = {
        URI_MATCH_GROUP,
        URI_MATCH_SOURCE,
        URI_MATCH_ARTICLE,
        URI_MATCH_IMAGE,
        URI_MATCH_WEIBO_ACCOUNT,
        URI_MATCH_GROUP_ID,
        URI_MATCH_SOURCE_ID,
        URI_MATCH_ARTICLE_ID,
        URI_MATCH_IMAGE_ID,
        URI_MATCH_WEIBO_ACCOUNT_ID,
    };
    
    // Content type
    private static final String[] CONTENT_TYPE = {
        "vnd.android.cursor.dir/" + TABLES[0],
        "vnd.android.cursor.dir/" + TABLES[1],
        "vnd.android.cursor.dir/" + TABLES[2],
        "vnd.android.cursor.dir/" + TABLES[3],
        "vnd.android.cursor.dir/" + TABLES[4],
        "vnd.android.cursor.item/" + TABLES[0],
        "vnd.android.cursor.item/" + TABLES[1],
        "vnd.android.cursor.item/" + TABLES[2],
        "vnd.android.cursor.item/" + TABLES[3],
        "vnd.android.cursor.item/" + TABLES[4],
    };
    
    // Paths
    private static final String[] URI_PATHS = {
        TABLES[0],
        TABLES[1],
        TABLES[2],
        TABLES[3],
        TABLES[4],
        TABLES[0] + "/#",
        TABLES[1] + "/#",
        TABLES[2] + "/#",
        TABLES[3] + "/#",
        TABLES[4] + "/#",
    };
    
    // URIs
    private static final Uri[] CONTENT_URIS = {
        Feed.Group.CONTENT_URI,
        Feed.Source.CONTENT_URI,
        Feed.Article.CONTENT_URI,
        Feed.Image.CONTENT_URI,
        Feed.WeiboAccount.CONTENT_URI,
    };
    
    private static final UriMatcher sUriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
    static {
        for (int i = 0; i < URI_INDICES.length; i++) {
            sUriMatcher.addURI(AUTHORITY, URI_PATHS[i], URI_INDICES[i]);
        }
    }
    
    @Override
    public int delete(Uri uri, String selection, String[] selectionArgs) {
        final int uriMatch = sUriMatcher.match(uri);
        if (uriMatch == -1) {
            throw new IllegalArgumentException("Delete bad uri " + uri);
        }
        final StringBuilder where = new StringBuilder();
        if (uriMatch >= URI_MATCH_ID_MINIMIUM && uriMatch <= URI_MATCH_ID_MAXIMIUM) {
            // query for id, we must extract id from uri and make it as a where clause
            where.append(" (" + Feed.CommonColumns._ID + "=" + uri.getPathSegments().get(1) + ") ");
        }
        if (!TextUtils.isEmpty(selection)) {
            if (where.length() > 0) {
                where.append(" AND ");
            }
            where.append(" (");
            where.append(selection);
            where.append(") ");
        }
        final SQLiteDatabase database = mOpenHelper.getWritableDatabase();
        int count = 0;
        switch (uriMatch) {
        case URI_MATCH_ARTICLE_ID:
        case URI_MATCH_ARTICLE: {
            // update article count for table 'source' and table 'group'
            // First, query the source_id and group_id, then delete articles and update sources and groups
            final Cursor c = database.query(TABLES[uriMatch%10], new String[]{Feed.Article.SOURCE_ID, Feed.Article.GROUP_ID}, 
                    where.toString(), selectionArgs, null, null, DEFAULT_SORT_ORDER);
            DatabaseUtils.dumpCursor(c);
            count = database.delete(TABLES[uriMatch % 10], where.toString(), selectionArgs);
            if (c != null && c.moveToFirst()) {
                do {
                    mOpenHelper.updateArticleCountForSource(c.getString(0));
                    mOpenHelper.updateArticleCountForGroup(c.getString(1));
                } while (c.moveToNext());
                c.close();
            }
            mOpenHelper.removeOrphanedImage();
            break;
        }
        case URI_MATCH_SOURCE:
        case URI_MATCH_SOURCE_ID: {
            // source is deleted, should also delete all articles belonging to this source
            // update article count for table 'groups'
            String w = null;
            if (!TextUtils.isEmpty(where)) {
                w = where.toString();
            }
            final Cursor c = database.query(TABLES[uriMatch%10], new String[]{Feed.Source._ID, Feed.Source.GROUP_ID}, 
                    w, selectionArgs, null, null, DEFAULT_SORT_ORDER);
            DatabaseUtils.dumpCursor(c);
            count = database.delete(TABLES[uriMatch % 10], where.toString(), selectionArgs);
            if (c != null && c.moveToFirst()) {
                do {
                    database.delete(TABLES[URI_MATCH_ARTICLE], "source_id=?", new String[]{c.getString(0)});
                    mOpenHelper.updateArticleCountForGroup(c.getString(1));
                } while (c.moveToNext());
                c.close();
            }
            mOpenHelper.removeOrphanedImage();
            break;
        }
        case URI_MATCH_GROUP:
        case URI_MATCH_GROUP_ID: {
            // should also delete all sources and articles belonging to this group
            String w = null;
            if (!TextUtils.isEmpty(where)) {
                w = where.toString();
            }
            final Cursor c = database.query(TABLES[uriMatch%10], new String[]{Feed.Group._ID}, w, selectionArgs, 
                    null, null, DEFAULT_SORT_ORDER);
            DatabaseUtils.dumpCursor(c);
            count = database.delete(TABLES[uriMatch % 10], where.toString(), selectionArgs);
            if (c != null && c.moveToFirst()) {
                do {
                    database.delete(TABLES[URI_MATCH_SOURCE], "group_id=?", new String[]{c.getString(0)});
                    database.delete(TABLES[URI_MATCH_ARTICLE], "group_id=?", new String[]{c.getString(0)});
                } while (c.moveToNext());
                c.close();
            }
            break;
        }
        case URI_MATCH_WEIBO_ACCOUNT:
        case URI_MATCH_WEIBO_ACCOUNT_ID:
            count = database.delete(TABLES[uriMatch%10], where.toString(), selectionArgs);
            break;
        default:
            throw new IllegalArgumentException("delete for uri '" + uri + "' is not supported");
        }
        getContext().getContentResolver().notifyChange(Feed.Article.CONTENT_URI, null);
        getContext().getContentResolver().notifyChange(Feed.Source.CONTENT_URI, null);
        getContext().getContentResolver().notifyChange(Feed.Group.CONTENT_URI, null);
        return count;
    }

    @Override
    public String getType(Uri uri) {
        final int uriMatch = sUriMatcher.match(uri);
        String ct = null;
        for (int i = 0; i < URI_INDICES.length; i++) {
            if (uriMatch <= URI_INDICES[i]) {
                ct = CONTENT_TYPE[i];
                break;
            }
        }
        return ct;
    }

    @Override
    public Uri insert(Uri uri, ContentValues values) {
        final int uriMatch = sUriMatcher.match(uri);
        if (uriMatch == -1) {
            throw new IllegalArgumentException("Insert bad uri " + uri);
        }
        final SQLiteDatabase database = mOpenHelper.getWritableDatabase();
        String defaultColumnHacked = Feed.CommonColumns.TITLE;
        if (uriMatch == URI_MATCH_IMAGE || uriMatch == URI_MATCH_IMAGE_ID || 
                uriMatch == URI_MATCH_WEIBO_ACCOUNT || uriMatch == URI_MATCH_WEIBO_ACCOUNT_ID) {
            defaultColumnHacked = Feed.CommonColumns._COUNT;
        }
        final long row = database.insert(TABLES[uriMatch % 10], defaultColumnHacked, values);
        final Uri rowUri = ContentUris.withAppendedId(CONTENT_URIS[uriMatch], row);
        Log.e(TAG, "row usirlsfd: " + rowUri);
        if (uriMatch == URI_MATCH_ARTICLE || uriMatch == URI_MATCH_ARTICLE_ID) {
            // try to update article count for source and group
            mOpenHelper.updateArticleCountForSource(values.getAsString(Feed.Article.SOURCE_ID));
            mOpenHelper.updateArticleCountForGroup(values.getAsString(Feed.Article.GROUP_ID));
        }
        downloadImage(uriMatch, rowUri);
        getContext().getContentResolver().notifyChange(rowUri, null);
        return rowUri;
    }

    private void downloadImage(final int uriMatch, final Uri rowUri) {
        if (rowUri.getPathSegments().size() < 2) {
            Log.e(TAG, "shit, bad uri " + rowUri);
            return;
        }
        if (uriMatch == URI_MATCH_SOURCE || uriMatch == URI_MATCH_SOURCE_ID ||
        	uriMatch == URI_MATCH_ARTICLE || uriMatch == URI_MATCH_ARTICLE_ID) {
            final Cursor c = mOpenHelper.getReadableDatabase().query(TABLES[uriMatch % 10], 
        	    new String[]{Feed.Article.IMAGE_URL, Feed.Article.IMAGE_ID}, "_ID =?", 
        	    new String[]{rowUri.getPathSegments().get(1)}, null, null, null);
            if (c == null || !c.moveToFirst()) {
        	return;
            }
            final String imageUrl = c.getString(0);
            final String imageId = c.getString(1);
            if (!TextUtils.isEmpty(imageUrl) && TextUtils.isEmpty(imageId)) {
                mDownloadServer.execute(rowUri);
            }
        }
    }

    @Override
    public boolean onCreate() {
        mOpenHelper = new FeedDatabaseHelper(getContext(), DATABASE_NAME, null, DATABASE_VERSION);
        mDownloadServer = new ImageDownloadServer(getContext().getApplicationContext());
        mDownloadServer.start();
        return false;
    }

    @Override
    public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortby) {
        if (TextUtils.isEmpty(sortby)) {
            sortby = DEFAULT_SORT_ORDER;
        }
        final int uriMatch = sUriMatcher.match(uri);
        if (uriMatch == -1) {
            throw new IllegalArgumentException("Query bad uri " + uri);
        }
        if (uriMatch == URI_MATCH_IMAGE || uriMatch == URI_MATCH_IMAGE_ID || 
                uriMatch == URI_MATCH_WEIBO_ACCOUNT || uriMatch == URI_MATCH_WEIBO_ACCOUNT_ID) {
            sortby = "_id ASC";
        }
        final SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
        queryBuilder.setTables(TABLES[uriMatch % 10]);
        if (uriMatch >= URI_MATCH_ID_MINIMIUM && uriMatch <= URI_MATCH_ID_MAXIMIUM) {
            // query for id, we must extract id from uri and make it as a where clause
            queryBuilder.appendWhere(Feed.CommonColumns._ID + "=" + uri.getPathSegments().get(1));
        }
        final Cursor cursor = queryBuilder.query(mOpenHelper.getReadableDatabase(), 
                projection, selection, selectionArgs, null, null, sortby);
        cursor.setNotificationUri(getContext().getContentResolver(), uri);
        return cursor;
    }

    @Override
    public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
        final int uriMatch = sUriMatcher.match(uri);
        if (uriMatch == -1) {
            throw new IllegalArgumentException("Insert bad uri " + uri);
        }
        final StringBuilder where = new StringBuilder();
        if (uriMatch >= URI_MATCH_ID_MINIMIUM && uriMatch <= URI_MATCH_ID_MAXIMIUM) {
            // query for id, we must extract id from uri and make it as a where clause
            where.append(" (" + Feed.CommonColumns._ID + "=" + uri.getPathSegments().get(1) + ") ");
        }
        if (!TextUtils.isEmpty(selection)) {
            if (where.length() > 0) {
                where.append(" AND ");
            }
            where.append(" (");
            where.append(selection);
            where.append(") ");
        }
        final int count = mOpenHelper.getWritableDatabase().update(TABLES[uriMatch % 10], 
                values, where.toString(), selectionArgs);
        // try to update article count for source and group
        if (uriMatch == URI_MATCH_ARTICLE || uriMatch == URI_MATCH_ARTICLE_ID) {
            final Cursor cursor = mOpenHelper.getWritableDatabase().query(TABLES[uriMatch%10], 
                    new String[]{Feed.Article.SOURCE_ID, Feed.Article.GROUP_ID}, where.toString(), selectionArgs, 
                    null, null, DEFAULT_SORT_ORDER);
            if (cursor != null && cursor.moveToFirst()) {
                mOpenHelper.updateArticleCountForSource(cursor.getString(0));
                mOpenHelper.updateArticleCountForGroup(cursor.getString(1));
                cursor.close();
            }
        }
        downloadImage(uriMatch, uri);
        getContext().getContentResolver().notifyChange(uri, null);
        return count;
    }

    @Override
    public ParcelFileDescriptor openFile(Uri uri, String mode) throws FileNotFoundException {
	ParcelFileDescriptor pfd = openFileHelper(uri, mode);
	return pfd;
    }

    private class FeedDatabaseHelper extends SQLiteOpenHelper {
        public FeedDatabaseHelper(Context context, String name,
                CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

	@Override
        public void onCreate(SQLiteDatabase db) {
            createTableGroup(db);
            createTableSource(db);
            createTableArticle(db);
            createTableImage(db);
            createTableWeiboAccount(db);
        }

        private void createTableGroup(SQLiteDatabase db) {
            final StringBuilder sql = new StringBuilder();
            sql.append("CREATE TABLE " + TABLES[0] + " (");
            sql.append(Feed.Group._ID);
            sql.append(" INTEGER PRIMARY KEY, ");
            sql.append(Feed.Group._COUNT);
            sql.append(" INTEGER DEFAULT 0, ");
            sql.append(Feed.Group.COUNT_UNREAD);
            sql.append(" INTEGER DEFAULT 0, ");
            sql.append(Feed.Group.TITLE);
            sql.append(" TEXT, ");
            sql.append(Feed.Group.DESCRIPTION);
            sql.append(" TEXT);");
            db.execSQL(sql.toString());
            // insert an default uncategorized group
            db.execSQL("INSERT into groups (_count,count_unread,title,description) VALUES(0,0, '" + 
                    Feed.Group.GROUP_DEFAULT + "', '" + Feed.Group.GROUP_DEFAULT + "');");
        }
        
        private void createTableSource(SQLiteDatabase db) {
            final StringBuilder sql = new StringBuilder();
            sql.append("CREATE TABLE " + TABLES[1] + " (");
            sql.append(Feed.Source._ID);
            sql.append(" INTEGER PRIMARY KEY, ");
            sql.append(Feed.Source._COUNT);
            sql.append(" INTEGER DEFAULT 0, ");
            sql.append(Feed.Group.COUNT_UNREAD);
            sql.append(" INTEGER DEFAULT 0, ");
            sql.append(Feed.Source.GROUP_ID);
            sql.append(" INTEGER, ");
            sql.append(Feed.Source.FEED_URL);
            sql.append(" TEXT, ");
            sql.append(Feed.Source.URL);
            sql.append(" TEXT, ");
            sql.append(Feed.Source.TITLE);
            sql.append(" TEXT, ");
            sql.append(Feed.Source.DESCRIPTION);
            sql.append(" TEXT, ");
            sql.append(Feed.Source.MODIFIED_DATE);
            sql.append(" TEXT, ");
            sql.append(Feed.Source.IMAGE_ID);
            sql.append(" INTEGER, ");
            sql.append(Feed.Source.IMAGE_URL);
            sql.append(" TEXT, ");
            sql.append(Feed.Source.UPDATING);
            sql.append(" INTEGER DEFAULT 0);");
            db.execSQL(sql.toString());

        }
        
        private void createTableArticle(SQLiteDatabase db) {
            final StringBuilder sql = new StringBuilder();
            sql.append("CREATE TABLE " + TABLES[2] + " (");
            sql.append(Feed.Article._ID);
            sql.append(" INTEGER PRIMARY KEY, ");
            sql.append(Feed.Article._COUNT);
            sql.append(" INTEGER, ");
            sql.append(Feed.Article.GROUP_ID);
            sql.append(" INTEGER, ");
            sql.append(Feed.Article.SOURCE_ID);
            sql.append(" INTEGER, ");
            sql.append(Feed.Article.READ);
            sql.append(" INTEGER DEFAULT 0, ");
            sql.append(Feed.Article.TITLE);
            sql.append(" TEXT, ");
            sql.append(Feed.Article.DESCRIPTION);
            sql.append(" TEXT, ");
            sql.append(Feed.Article.CONTENT);
            sql.append(" TEXT, ");
            sql.append(Feed.Article.ARTICLE_URL);
            sql.append(" TEXT, ");
            sql.append(Feed.Article.PUB_DATE);
            sql.append(" LONG, ");
            sql.append(Feed.Article.IMAGE_ID);
            sql.append(" INTEGER, ");
            sql.append(Feed.Article.IMAGE_URL);
            sql.append(" TEXT);");
            db.execSQL(sql.toString());
            
        }
        
        private void createTableImage(SQLiteDatabase db) {
            final StringBuilder sql = new StringBuilder();
            sql.append("CREATE TABLE " + TABLES[3] + " (");
            sql.append(Feed.Image._ID);
            sql.append(" INTEGER PRIMARY KEY, ");
            sql.append(Feed.Image._DATA);
            sql.append(" TEXT);");
            db.execSQL(sql.toString());
        }

        private void createTableWeiboAccount(SQLiteDatabase db) {
            final StringBuilder sql = new StringBuilder();
            sql.append("CREATE TABLE " + TABLES[4] + " (");
            sql.append(Feed.WeiboAccount._ID);
            sql.append(" INTEGER PRIMARY KEY, ");
            sql.append(Feed.WeiboAccount._COUNT);
            sql.append(" INTEGER DEFAULT 0, ");
            sql.append(Feed.WeiboAccount.PROVIDER);
            sql.append(" TEXT, ");
            sql.append(Feed.WeiboAccount.USER_ID);
            sql.append(" TEXT, ");
            sql.append(Feed.WeiboAccount.ACCESS_TOKEN);
            sql.append(" TEXT, ");
            sql.append(Feed.WeiboAccount.ACCESS_SECRET);
            sql.append(" TEXT);");
            db.execSQL(sql.toString());
        }
        
        public void updateArticleCountForSource(String sourceId) {
            if (TextUtils.isEmpty(sourceId)) {
                Log.e(TAG, "updateArticleCountForSource, sourceId is emtpy, do nothing");
                return;
            }
            getWritableDatabase().execSQL("UPDATE sources SET _count = " + 
                    "(SELECT COUNT(_id) FROM articles WHERE source_id= " + sourceId + ") " + 
                    " WHERE sources._id = " + sourceId + ";");
            getWritableDatabase().execSQL("UPDATE sources SET count_unread = " + 
                    " (SELECT COUNT(_id) FROM articles WHERE source_id= " + sourceId + " AND read=0) " +
                    " WHERE sources._id = " + sourceId + ";");
            getContext().getContentResolver().notifyChange(
                    ContentUris.withAppendedId(Feed.Source.CONTENT_URI, Long.valueOf(sourceId)), null);
        }
        
        public void updateArticleCountForGroup(String groupId) {
            if (TextUtils.isEmpty(groupId)) {
                Log.e(TAG, "updateArticleCountForGroup, sourceId is emtpy, do nothing");
                return;
            }
            getWritableDatabase().execSQL("UPDATE groups SET _count = " + 
                    "(SELECT COUNT(_id) FROM articles WHERE group_id=" + groupId + ") " + 
                    " WHERE groups._id = " + groupId + ";");
            getWritableDatabase().execSQL("UPDATE groups SET count_unread = " +
                    "(SELECT COUNT(_id) FROM articles WHERE group_id=" + groupId + " AND read=0) " + 
                    " WHERE groups._id = " + groupId + ";");
            getContext().getContentResolver().notifyChange(
                    ContentUris.withAppendedId(Feed.Group.CONTENT_URI, Long.valueOf(groupId)), null);
        }

        public void removeOrphanedImage() {
	    getWritableDatabase().execSQL("DELETE FROM images WHERE _id NOT IN (SELECT DISTINCT image_id FROM articles)");
	    getWritableDatabase().execSQL("DELETE FROM images WHERE _id NOT IN (SELECT DISTINCT image_id FROM sources)");
	    // TODO: should also remove the image on sdcard?
	}
        
        @Override
        public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
            // Nothing to do right now
        }
    }
}